package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.Voucher;
import com.turing.post.organization.util.VoucherDao;
import com.turing.post.organization.util.VoucherStock;
import com.turing.post.organization.util.VoucherStockDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author wzd
 * @2013-03-12
 */
@Stateless(mappedName = "organization.impl.VoucherStockDaoImpl")
@Remote
@Local
public class VoucherStockDaoImpl implements VoucherStockDao {
	@EJB(mappedName = "organization.impl.VoucherDaoImpl")
	private VoucherDao voucherDaoImpl;// 凭证种类表
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * 根据条件查询凭证库存表列表
	 * 
	 * @param toBranchNo
	 *            调入机构号
	 * @param status
	 *            状态
	 * @param voucherId
	 *            凭证种类ID
	 * @param voucherNo
	 *            凭证号
	 * @param page
	 *            页码
	 * @return 凭证库存表列表
	 */
	@SuppressWarnings("unchecked")
	public List<VoucherStock> getVoucherStockList(PCPSWebInput webInput,
			String BranchNo, String status, String voucherId, String startNo,
			String endNo, Boolean isSingle, Integer page) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;

		Voucher voucher = voucherDaoImpl.getVoucherByVoucherId(new BigInteger(
				voucherId));
		if (voucher == null)
			return null;
		int vouchernoilength = voucher.getVoucherNoLen().intValue();
		if (!PCPSUtil.isEmpty(startNo)) {
			startNo = String.format("%0" + vouchernoilength + "d",
					new BigInteger(startNo));
		}
		if (!PCPSUtil.isEmpty(endNo)) {
			endNo = String.format("%0" + vouchernoilength + "d",
					new BigInteger(endNo));
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select * from VoucherStock where IssueCompanyNo='"
				+ issuecompanyno + "' ";

		if (!PCPSUtil.isEmpty(BranchNo)) {
			if (isSingle == false)
				sql = sql
						+ "and (SALEBRANCHNO in (select subBranchNo from sysbranchsubord t where t.branchno='"
						+ BranchNo
						+ "') or ISSUEBRANCHNO in (select subBranchNo from sysbranchsubord t where t.branchno='"
						+ BranchNo + "'))";
			else
				sql = sql + "and (SALEBRANCHNO ='" + BranchNo
						+ "' or ISSUEBRANCHNO ='" + BranchNo + "')";
		}
		if (!PCPSUtil.isEmpty(startNo))
			sql = sql + " and VoucherNo >=" + startNo + "";
		if (!PCPSUtil.isEmpty(endNo)) {
			sql = sql + " and VoucherNo <=" + endNo + "";
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + " and status = '" + status + "'";
		}
		if (!PCPSUtil.isEmpty(voucherId)) {
			sql = sql + " and voucherId = '" + voucherId + "'";
		}
		sql = sql + " order by voucherId,Status,VoucherNo";
		Query query = _em.createNativeQuery(sql, VoucherStock.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	/**
	 * 根据条件查询凭证库存表记录数
	 * 
	 * @param toBranchNo
	 *            调入机构号
	 * @param status
	 *            状态
	 * @param voucherId
	 *            凭证种类ID
	 * @param voucherNo
	 *            凭证号
	 * @param page
	 * @return 凭证库存表录数
	 */
	@Override
	public BigInteger getVoucherStockListTotal(PCPSWebInput webInput,
			String BranchNo, String status, String voucherId, String startNo,
			String endNo, Boolean isSingle) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select nvl(count(*),0) from VoucherStock where IssueCompanyNo='"
				+ issuecompanyno + "' ";
		if (!PCPSUtil.isEmpty(BranchNo)) {
			if (isSingle == false)
				sql = sql
						+ "and (SALEBRANCHNO in (select subBranchNo from sysbranchsubord t where t.branchno='"
						+ BranchNo
						+ "') or ISSUEBRANCHNO in (select subBranchNo from sysbranchsubord t where t.branchno='"
						+ BranchNo + "'))";
			else
				sql = sql + "and (SALEBRANCHNO ='" + BranchNo
						+ "' or ISSUEBRANCHNO ='" + BranchNo + "')";
		}
		if (!PCPSUtil.isEmpty(startNo))
			sql = sql + " and VoucherNo >=" + startNo + "";
		if (!PCPSUtil.isEmpty(endNo)) {
			sql = sql + " and VoucherNo <=" + endNo + "";
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + " and status = '" + status + "'";
		}
		if (!PCPSUtil.isEmpty(voucherId)) {
			sql = sql + " and voucherId = '" + voucherId + "'";
		}
		sql = sql + "order by voucherId,Status";
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	/**
	 * 新增凭证库存表
	 * 
	 * @param VoucherStock
	 *            凭证库存表
	 * @throws PCPSApplicationException
	 */
	@Override
	public void insertVoucherStock(VoucherStock voucherStock)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, voucherStock);
	}

	/**
	 * 修改凭证库存表状态
	 * 
	 * @param VoucherStock
	 *            凭证库存
	 * @throws PCPSApplicationException
	 */
	public void updateVoucherStockByStatus(VoucherStock voucherStock)
			throws PCPSApplicationException {
		GeneralDao.update(_em, voucherStock);
	}

	/**
	 * 删除凭证库存表
	 * 
	 * @param VoucherStock
	 *            凭证库存
	 * @throws PCPSApplicationException
	 */
	public void deleteVoucherStock(VoucherStock voucherStock)
			throws PCPSApplicationException {
		VoucherStock vbb = getVoucherStock(voucherStock);
		if (vbb != null) {
			GeneralDao.remove(_em, vbb);
		}
	}

	/**
	 * 根据条件查询凭证库存列表对象
	 * 
	 * @param VoucherStock
	 *            凭证调拨单id
	 * @throws PCPSApplicationException
	 */
	public VoucherStock getVoucherStock(VoucherStock voucherStock) {
		return _em.find(VoucherStock.class, voucherStock);
	}

	public VoucherStock getVoucherStockForUpdate(VoucherStock voucherStock) {
		String sql = "select * from VoucherStock t where t.voucherid=?1 and t.voucherno=?2 for update";
		Query query = _em.createNativeQuery(sql, VoucherStock.class);
		query.setParameter(1, voucherStock.getVoucherId());
		query.setParameter(2, voucherStock.getVoucherNo());
		return (VoucherStock) query.getSingleResult();
	}

	/**
	 * 根据凭证种类ID和凭证号检索库存数量
	 * 
	 * @param voucherId
	 *            凭证种类id
	 * @param StartVoucherNo
	 *            起始凭证号
	 * @param EndVoucherNo
	 *            终止凭证号
	 * @throws PCPSApplicationException
	 */
	public BigInteger getVoucherStockListTotal(PCPSWebInput webInput,
			BigInteger voucherId, String StartVoucherNo, String EndVoucherNo,
			String Status) {
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select nvl(count(*),0) from VoucherStock where IssueCompanyNo=?1 and (issueBranchNo=?2 or SaleBranchNo=?2) "
				+ " and VoucherNo>=?3 and VoucherNo<=?4 and  (Status = ?5 or ?5 is NULL)  and (voucherId = ?6 or ?6 is NULL)";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, webInput.getOperBranchNo());
		query.setParameter(3, StartVoucherNo);
		query.setParameter(4, EndVoucherNo);
		query.setParameter(5, Status);
		query.setParameter(6, voucherId);
		return new BigInteger(query.getSingleResult().toString());
	}

	/**
	 * 根据凭证种类ID和凭证号检索库存凭证种类列表
	 * 
	 * @param voucherId
	 *            凭证种类id
	 * @param StartVoucherNo
	 *            起始凭证号
	 * @param EndVoucherNo
	 *            终止凭证号
	 * @throws PCPSApplicationException
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getVoucherStockVoucherIdList(PCPSWebInput webInput,
			String StartVoucherNo, String EndVoucherNo) {
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select distinct(VoucherId) from VoucherStock where IssueCompanyNo='"
				+ issuecompanyno
				+ "' and VoucherNo>="
				+ StartVoucherNo
				+ " and VoucherNo<=" + EndVoucherNo + " and Status='0'";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	/**
	 * 根据条件查询凭证库存表列表--不分页
	 * 
	 * @param status
	 *            状态
	 * @param voucherId
	 *            凭证种类id
	 * @param srartVoucherNo
	 *            起始凭证号
	 * @param endVoucherNo
	 *            终止凭证号
	 * @return 凭证库存表列表
	 */
	@SuppressWarnings("unchecked")
	public List<VoucherStock> getVoucherStockListNoPage(PCPSWebInput webInput,
			String srartVoucherNo, String endVoucherNo, String status,
			BigInteger voucherId) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select * from VoucherStock where IssueCompanyNo='"
				+ issuecompanyno + "' and status = '" + status
				+ "' and voucherId=" + voucherId + "";
		if (!PCPSUtil.isEmpty(srartVoucherNo)) {
			sql = sql + " and VoucherNo >= " + srartVoucherNo + "";
		}
		if (!PCPSUtil.isEmpty(endVoucherNo)) {
			sql = sql + " and voucherNo <= " + endVoucherNo + "";
		}
		sql = sql + " order by VoucherNo";
		Query query = _em.createNativeQuery(sql, VoucherStock.class);
		return query.getResultList();
	}

	/**
	 * 根据条件查询凭证库存表列表
	 * 
	 * @param toBranchNo
	 *            调入机构号
	 * @param status
	 *            状态
	 * @param voucherId
	 *            凭证种类ID
	 * @param voucherNo
	 *            凭证号
	 * @param page
	 *            页码
	 * @return 凭证库存表列表
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getVoucherStockIndexList(PCPSWebInput webInput,
			String BranchNo, String voucherId) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select VOUCHERID,count(*) total,SUM(CASE WHEN STATUS='"
				+ VoucherStock.Status_0 + "' THEN 1 ELSE 0 END) AS STATUS_0,"
				+ " SUM(CASE WHEN STATUS='" + VoucherStock.Status_1
				+ "' THEN 1 ELSE 0 END) AS STATUS_1,"
				+ " SUM(CASE WHEN STATUS='" + VoucherStock.Status_2
				+ "' THEN 1 ELSE 0 END) AS STATUS_2,"
				+ " SUM(CASE WHEN STATUS='" + VoucherStock.Status_9
				+ "' THEN 1 ELSE 0 END) AS STATUS_9"
				+ " from VoucherStock where ISSUECOMPANYNO = ?1";
		sql += " and (?2 is null or ?2=VOUCHERID)";
		if (webInput.isAll() == false) {
			sql += "and (SALEBRANCHNO in (select subBranchNo from sysbranchsubord t where t.branchno=?3) or ISSUEBRANCHNO in (select subBranchNo from sysbranchsubord t where t.branchno=?3))";
		} else {
			sql += "and (SALEBRANCHNO=?3 or ISSUEBRANCHNO=?3)";
		}
		sql += " group by VOUCHERID  order by VOUCHERID";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, voucherId);
		query.setParameter(3, BranchNo);
		List<Object[]> result = query.getResultList();
		return result;
	}

	// 检索凭证种类列表
	// @SuppressWarnings("unchecked")
	// private List<String> getVoucherlist(PCPSWebInput webInput,
	// String IssueBranchNo, String SaleBranchNo, String status,String isSingle)
	// {
	// if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
	// return null;
	// String issuecompanyno = PCPSUtil.getIssueCompanyNo(webInput);
	// String sql =
	// "select distinct(to_char(VoucherId)) as vcid from VoucherStock where
	// IssueCompanyNo='"
	// + issuecompanyno + "' ";
	// if (!PCPSUtil.isEmpty(IssueBranchNo))
	// if(isSingle!=null && isSingle.equals("0"))
	// sql = sql + " and issueBranchNo like '" + IssueBranchNo + "%'";
	// else
	// sql = sql + " and issueBranchNo='" + IssueBranchNo + "'";
	// if (!PCPSUtil.isEmpty(SaleBranchNo)) {
	// if(isSingle!=null && isSingle.equals("0"))
	// sql = sql + " and SaleBranchNo like '" + SaleBranchNo + "%'";
	// else
	// sql = sql + " and SaleBranchNo = '" + SaleBranchNo + "'";
	// }
	// if (!PCPSUtil.isEmpty(status)) {
	// sql = sql + " and status = '" + status + "'";
	// }
	// sql = sql + " order by voucherId asc";
	// Query query = _em.createNativeQuery(sql);
	// List<String> obj_list = query.getResultList();
	// return obj_list;
	// }
	/**
	 * 根据凭证种类ID和凭证号检索库存数量
	 * 
	 * @param voucherId
	 *            凭证种类id
	 * @param StartVoucherNo
	 *            起始凭证号
	 * @param EndVoucherNo
	 *            终止凭证号
	 * @throws PCPSApplicationException
	 */
	public BigInteger getVoucherStockListByStatusTotal(PCPSWebInput webInput,
			BigInteger voucherId, String StartVoucherNo, String EndVoucherNo,
			String Status) {
		String sql = "select nvl(count(*),0) from VoucherStock where  ISSUECOMPANYNO='"
				+ webInput.getOperBranchNo().substring(0, 6)
				+ "'"
				+ " and VoucherNo>="
				+ StartVoucherNo
				+ " and VoucherNo<="
				+ EndVoucherNo + " ";
		if (!PCPSUtil.isEmpty(Status)) {
			sql = sql + " and Status='" + Status + "'";
		}
		if (voucherId != null) {
			sql = sql + " and voucherId=" + voucherId + " ";
		}

		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	/**
	 * 根据凭证种类ID和凭证号检索库存数量
	 * 
	 * @param voucherId
	 *            凭证种类id
	 * @param StartVoucherNo
	 *            起始凭证号
	 * @param EndVoucherNo
	 *            终止凭证号
	 * @param issuebranchno
	 *            发卡机构编号
	 * @throws PCPSApplicationException
	 */
	public BigInteger getVoucherStockListByBranchNoTotal(String issuebranchno,
			BigInteger voucherId, String StartVoucherNo, String EndVoucherNo) {
		String sql = "select nvl(count(*),0) from VoucherStock where 1=1 "
				+ " and VoucherNo>=" + StartVoucherNo + " and VoucherNo<="
				+ EndVoucherNo + " ";
		if (!PCPSUtil.isEmpty(issuebranchno)) {
			sql = sql + " and issuebranchno='" + issuebranchno + "'";
		}
		if (voucherId != null) {
			sql = sql + " and voucherId=" + voucherId + " ";
		}
		// System.out.println(sql);
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public VoucherStock getVoucherStockByVoucherNo(String voucherNo,
			PCPSWebInput input) {
		if (PCPSUtil.isEmpty(voucherNo) || input == null
				|| PCPSUtil.isEmpty(input.getOperBranchNo()))
			return null;
		String sql = "select * from VoucherStock t where t.voucherno=?1 "
				+ " and (t.issuebranchno = ?2 or t.salebranchno = ?2 ) "
				+ " and t.status = " + VoucherStock.Status_0;
		Query query = _em.createNativeQuery(sql, VoucherStock.class);
		query.setParameter(1, voucherNo);
		query.setParameter(2, input.getOperBranchNo());
		List<VoucherStock> list = query.getResultList();
		if (list == null || list.size() <= 0)
			return null;
		return list.get(0);
	}

	/**
	 * 根据条件查询凭证库存表列表
	 * 
	 * @param toBranchNo
	 *            调入机构号
	 * @param status
	 *            状态
	 * @param voucherId
	 *            凭证种类ID
	 * @param voucherNo
	 *            凭证号
	 * @return 凭证库存表号段列表
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getVoucherStockByCon(PCPSWebInput webInput,
			String BranchNo, String status, String voucherId, String startNo,
			String endNo, Boolean isSingle) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		Voucher voucher = voucherDaoImpl.getVoucherByVoucherId(new BigInteger(
				voucherId));
		if (voucher == null)
			return null;
		int vouchernoilength = voucher.getVoucherNoLen().intValue();
		if (!PCPSUtil.isEmpty(startNo)) {
			startNo = String.format("%0" + vouchernoilength + "d",
					new BigInteger(startNo.trim()));
		}
		if (!PCPSUtil.isEmpty(endNo)) {
			endNo = String.format("%0" + vouchernoilength + "d",
					new BigInteger(endNo.trim()));
		}
		String sql = "with v as( select c.voucherno ,nvl(c.issuebranchno,'')||nvl(c.salebranchno,'') as branchno,"
                   + " nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno as vb,"
                   + " lag (nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno ) over(order by c.voucherno) as lag,"
				   + " lead(nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno ) over(order by c.voucherno) as lead  "
				   + " from voucherstock c where c.issuecompanyno=?1 and c.voucherId=?2 and c.status =?4";
		if (!PCPSUtil.isEmpty(BranchNo)) {
			if (isSingle == false)
				sql = sql+" and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,'')) in (select subBranchNo from sysbranchsubord t where t.branchno=?3 )";
			else
				sql = sql + " and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,'')) = ?3 )";
		}
		if (!PCPSUtil.isEmpty(startNo))
			sql = sql + " and c.VoucherNo >=" + startNo + "";
		if (!PCPSUtil.isEmpty(endNo)) {
			sql = sql + " and c.VoucherNo <=" + endNo + "";
		}
		sql = sql
				+ " order by c.voucherno)"
				+ " select v1.voucherno, v2.voucherno,v1.branchno,v2.voucherno-v1.voucherno+1"
                + " from ( select v.voucherno,v.branchno,rownum as rn from v where ( v.vb  - v.lag ) != 1 or v.lag is null) v1,"
                + " ( select v.voucherno,v.branchno,rownum as rn from v where ( v.lead - v.vb ) != 1 or v.lead is null) v2 "
                + " where v1.rn = v2.rn  order by branchno desc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, webInput.getOperBranchNo().substring(0, 6));
		query.setParameter(2, voucherId);
		query.setParameter(3, BranchNo);
		query.setParameter(4, status);
		List<Object[]> objlists = query.getResultList();
		return objlists;
	}
	/**
	 * 根据凭证种类，机构号，发卡商,状态查询,
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getVoucherStockByBrNo(PCPSWebInput webInput,
			String BranchNo,String voucherId){
		
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;		
		List<Object[]> objlist=new ArrayList();
		if(PCPSUtil.isEmpty(voucherId)){
			List<Voucher> list=voucherDaoImpl.getAllVoucherByIssueCompanyNo(webInput.getOperBranchNo().substring(0,6));
			Voucher voucher=null;
			for(int i=0;i<list.size();i++){
				voucher=list.get(i);
				String sql = "with v as( select c.voucherno ,nvl(c.issuebranchno,'')||nvl(c.salebranchno,'') as branchno,"
					+ "  nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno as vb,lag (nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno ) over(order by c.voucherno) as lag,"
					+ " lead(nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno ) over(order by c.voucherno) as lead,c.status,c.voucherid"
					+ " from voucherstock c where c.issuecompanyno =?1  and c.voucherId=?2 ";
			
					if (webInput.isAll() == false)
						sql = sql+" and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,'')) in (select subBranchNo from sysbranchsubord t where t.branchno=?3 )";
					else
						sql = sql + " and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,'')) = ?3 )";
		
				sql = sql
						+ " order by c.voucherno)"
						+ " select v1.voucherno, v2.voucherno,v1.branchno,v2.voucherno-v1.voucherno+1,v1.status,v1.voucherid "
						+ " from ( select v.voucherno,v.branchno,v.status,v.voucherid,rownum as rn from v where ( v.vb  - v.lag ) != 1 or v.lag is null) v1,"
						+ " ( select v.voucherno,v.branchno,v.status,v.voucherid,rownum as rn from v where ( v.lead - v.vb ) != 1 or v.lead is null) v2   "
						+ " where v1.rn = v2.rn    order by branchno desc";
				Query query = _em.createNativeQuery(sql);
				query.setParameter(1, webInput.getOperBranchNo().substring(0, 6));
				query.setParameter(2, voucher.getVoucherId());
				query.setParameter(3, BranchNo);
				objlist.addAll(query.getResultList());				
			}
		}else{
			String sql = "with v as( select c.voucherno ,nvl(c.issuebranchno,'')||nvl(c.salebranchno,'') as branchno,"
				+ "  nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno as vb,lag (nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno ) over(order by c.voucherno) as lag,"
				+ " lead(nvl(c.issuebranchno,'') || nvl(c.salebranchno,'') || c.voucherno ) over(order by c.voucherno) as lead,c.status,c.voucherid"
				+ " from voucherstock c where c.issuecompanyno =?1 and c.voucherId=?2 ";
		
				if (webInput.isAll() == false)
					sql = sql+" and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,'')) in (select subBranchNo from sysbranchsubord t where t.branchno=?3 )";
				else
					sql = sql + " and (nvl(c.issuebranchno,'')||nvl(c.salebranchno,'')) = ?3 )";
	
			sql = sql
					+ " order by c.voucherno)"
					+ " select v1.voucherno, v2.voucherno,v1.branchno,v2.voucherno-v1.voucherno+1,v1.status,v1.voucherid "
					+ " from ( select v.voucherno,v.branchno,v.status,v.voucherid,rownum as rn from v where ( v.vb  - v.lag ) != 1 or v.lag is null) v1,"
					+ " ( select v.voucherno,v.branchno,v.status,v.voucherid,rownum as rn from v where ( v.lead - v.vb ) != 1 or v.lead is null) v2   "
					+ " where v1.rn = v2.rn    order by branchno desc";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, webInput.getOperBranchNo().substring(0, 6));
			query.setParameter(2, voucherId);	
			query.setParameter(3, BranchNo);
			objlist = query.getResultList();
		}
		
		
		return objlist;
	}
}
